//DevNETCoder
//CopyRight By DevNET Group
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using STT.Entities;
using STT.DataAccess;
 
namespace STT.Business
{
    public class BTbKhoaHoc
    {
//-----------------------------------------------------------//
        public static DataTable SelectAll()
        {
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbKhoaHoc_SelectAll1", null);
            return all;
        }
        public static DataTable SelectAll_Xuat(int maKH,string nguoitao)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter(@"MaKH", maKH);
            pr[1] = new SqlParameter(@"NguoiTao", nguoitao);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbKhoaHoc_SelectAll_Xuat", pr);
            return all;
        }
        public static DataTable SelectKhoaHocByMagv(string Magv)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"id", Magv);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "[TbKhoaHoc_GetByMaGV]", pr);
            return all;
        }
        public static DataTable SelectTop(int Top)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"Top", Top);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbKhoaHoc_SelectTop", pr);
            return all;
        }
        public static string LayTGBatDau(string MaKhoaHoc)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter(@"MaKhoaHoc", MaKhoaHoc);
            pr[1] = new SqlParameter(@"ReturnValue", SqlDbType.NChar);
            pr[1].Size = 6;
            pr[1].Direction = ParameterDirection.Output;

            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "TbKhoaHoc_LayTGBatDau", pr);
            return pr[1].Value.ToString();
        }
        public static DataTable SelectByID_MaLop(int MaLop)
        {

            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"MaLop", MaLop);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbKhoaHoc_SelectByID_MaLop", pr);
            return all;
        }
        public static DataTable SelectYear()
        {
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "SelectYear", null);
            return all;
        }
//-----------------------------------------------------------//
        public static void Insert(ETbKhoaHoc OTbKhoaHoc)
        {
            SqlParameter[] pr = new SqlParameter[7];
            pr[0] = new SqlParameter(@"TenKhoaHoc", OTbKhoaHoc.TenKhoaHoc);
            pr[1] = new SqlParameter(@"NoiDung", OTbKhoaHoc.NoiDung);
            pr[2] = new SqlParameter(@"DiaDiemHoc", OTbKhoaHoc.DiaDiemHoc);
            pr[3] = new SqlParameter(@"ThoiGianBatDau", OTbKhoaHoc.ThoiGianBatDau);
            pr[4] = new SqlParameter(@"ThoiGianKetThuc", OTbKhoaHoc.ThoiGianKetThuc);
            pr[5] = new SqlParameter(@"FileDinhKem", OTbKhoaHoc.FileDinhKem);
            pr[6] = new SqlParameter(@"NguoiTao", OTbKhoaHoc.NguoiTao);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure,"TbKhoaHoc_Insert", pr);
        }
        public static void InsertNull(ETbKhoaHoc OTbKhoaHoc)
        {
            SqlParameter[] pr = new SqlParameter[6];
            pr[0] = new SqlParameter(@"TenKhoaHoc", OTbKhoaHoc.TenKhoaHoc);
            pr[1] = new SqlParameter(@"NoiDung", OTbKhoaHoc.NoiDung);
            pr[2] = new SqlParameter(@"DiaDiemHoc", OTbKhoaHoc.DiaDiemHoc);
            pr[3] = new SqlParameter(@"ThoiGianBatDau", OTbKhoaHoc.ThoiGianBatDau);
            //pr[4] = new SqlParameter(@"ThoiGianKetThuc", OTbKhoaHoc.ThoiGianKetThuc);
            pr[4] = new SqlParameter(@"FileDinhKem", OTbKhoaHoc.FileDinhKem);
            pr[5] = new SqlParameter(@"NguoiTao", OTbKhoaHoc.NguoiTao);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "TbKhoaHoc_InsertNull", pr);
        }
//-----------------------------------------------------------//
        public static void Update(ETbKhoaHoc OTbKhoaHoc)
        {
            SqlParameter[] pr = new SqlParameter[7];
            pr[0] = new SqlParameter(@"MaKhoaHoc", OTbKhoaHoc.MaKhoaHoc);
            pr[1] = new SqlParameter(@"TenKhoaHoc", OTbKhoaHoc.TenKhoaHoc);
            pr[2] = new SqlParameter(@"NoiDung", OTbKhoaHoc.NoiDung);
            pr[3] = new SqlParameter(@"DiaDiemHoc", OTbKhoaHoc.DiaDiemHoc);
            pr[4] = new SqlParameter(@"ThoiGianBatDau", OTbKhoaHoc.ThoiGianBatDau);
            pr[5] = new SqlParameter(@"ThoiGianKetThuc", OTbKhoaHoc.ThoiGianKetThuc);
            pr[6] = new SqlParameter(@"FileDinhKem", OTbKhoaHoc.FileDinhKem);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure,"TbKhoaHoc_Update", pr);
        }
//-----------------------------------------------------------//
        public static void Delete(int MaKhoaHoc)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"MaKhoaHoc",MaKhoaHoc);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "TbKhoaHoc_Delete", pr);
        }
//-----------------------------------------------------------//
        public static DataTable SearchTenKhoaHoc(ETbKhoaHoc KhoaHoc)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter(@"TenKhoaHoc", KhoaHoc.TenKhoaHoc);
            pr[1] = new SqlParameter(@"NguoiTao", KhoaHoc.NguoiTao);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbKhoaHoc_Search", pr);
            return all;
        }

        public static DataTable SelectCoursesBySearch(ETbKhoaHoc KHoaHoc, string TenGiangVien, int Number, string Class, string NamHoc,string TenDN)
        {
            SqlParameter[] pr = new SqlParameter[10];
            pr[0] = new SqlParameter(@"IdCourses", KHoaHoc.MaKhoaHoc == -1 ? (object)DBNull.Value : KHoaHoc.MaKhoaHoc.ToString());
            pr[1] = new SqlParameter(@"NameCourses", KHoaHoc.TenKhoaHoc == "" ? (object)DBNull.Value : KHoaHoc.TenKhoaHoc);
            pr[2] = new SqlParameter(@"NameTeachers", TenGiangVien == "" ? (object)DBNull.Value : TenGiangVien);
            pr[3] = new SqlParameter(@"NumberStudent", Number == -1 ? (object)DBNull.Value : Number.ToString());
            pr[4] = new SqlParameter(@"AddressLearn", KHoaHoc.DiaDiemHoc == "" ? (object)DBNull.Value : KHoaHoc.DiaDiemHoc.ToString());
            //pr[5] = new SqlParameter(@"IdUnit", Number == -1 ? (object)DBNull.Value : Number.ToString());
            pr[5] = new SqlParameter(@"NgayBatDau", KHoaHoc.ThoiGianBatDau == default(DateTime) ? (object)DBNull.Value : KHoaHoc.ThoiGianBatDau.ToString("dd/MM/yyyy"));
            pr[6] = new SqlParameter(@"NgayKetThuc", KHoaHoc.ThoiGianKetThuc == default(DateTime) ? (object)DBNull.Value : KHoaHoc.ThoiGianKetThuc.ToString("dd/MM/yyyy"));
            pr[7] = new SqlParameter(@"Class", Class == "" ? (object)DBNull.Value : Class);
            pr[8] = new SqlParameter(@"NamHoc", NamHoc == "" ? (object)DBNull.Value : NamHoc);
            pr[9] = new SqlParameter(@"TenDN", TenDN);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "SelectCoursesBySearch", pr);
            return all;
        }
        public static DataTable StatisticsCoursesBySearch(ETbKhoaHoc KHoaHoc, string TenGiangVien, int Number, string Class, string NamHoc)
        {
            SqlParameter[] pr = new SqlParameter[9];
            pr[0] = new SqlParameter(@"IdCourses", KHoaHoc.MaKhoaHoc == -1 ? (object)DBNull.Value : KHoaHoc.MaKhoaHoc.ToString());
            pr[1] = new SqlParameter(@"NameCourses", KHoaHoc.TenKhoaHoc == "" ? (object)DBNull.Value : KHoaHoc.TenKhoaHoc);
            pr[2] = new SqlParameter(@"NameTeachers", TenGiangVien == "" ? (object)DBNull.Value : TenGiangVien);
            pr[3] = new SqlParameter(@"NumberStudent", Number == -1 ? (object)DBNull.Value : Number.ToString());
            pr[4] = new SqlParameter(@"AddressLearn", KHoaHoc.DiaDiemHoc == "" ? (object)DBNull.Value : KHoaHoc.DiaDiemHoc.ToString());
            pr[5] = new SqlParameter(@"NgayBatDau", KHoaHoc.ThoiGianBatDau == default(DateTime) ? (object)DBNull.Value : KHoaHoc.ThoiGianBatDau.ToString("dd/MM/yyyy"));
            pr[6] = new SqlParameter(@"NgayKetThuc", KHoaHoc.ThoiGianKetThuc == default(DateTime) ? (object)DBNull.Value : KHoaHoc.ThoiGianKetThuc.ToString("dd/MM/yyyy"));
            pr[7] = new SqlParameter(@"Class", Class == "" ? (object)DBNull.Value : Class);
            pr[8] = new SqlParameter(@"NamHoc", NamHoc == "" ? (object)DBNull.Value : NamHoc);

            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "StatisticsCoursesBySearch", pr);
            return all;
        }
        public static DataTable SelectAllByDonVi(string DonVi)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"id", int.Parse(DonVi));
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbKhoaHoc_ByDonViGDNV", pr);
            return all;
        }

        public static DataTable StatisticsCoursesByYear(string NamHoc,string TenDN)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter(@"Year", NamHoc == "-1" ? (object)DBNull.Value : NamHoc);
            pr[1] = new SqlParameter(@"TenDN", TenDN);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "SelectCoursesByYear", pr);
            return all;
        }
        //-----------------------------------------------------------//
        public static bool KiemTraDuLieu_Sua(ETbKhoaHoc KhoaHoc)
        {
            SqlParameter[] pr = new SqlParameter[3];
            pr[0] = new SqlParameter(@"TenKhoaHoc", KhoaHoc.TenKhoaHoc);
            pr[1] = new SqlParameter(@"ThoiGianBatDau", KhoaHoc.ThoiGianBatDau);
            pr[2] = new SqlParameter(@"MaKhoaHoc", KhoaHoc.MaKhoaHoc);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbKhoaHoc_KiemTraDuLieu_Sua", pr);
            if (all.Rows.Count > 0)
            {
                return true;
            }
            return false;
        }
//-----------------------------------------------------------//
        public static bool KiemTraDuLieu(ETbKhoaHoc KhoaHoc)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter(@"TenKhoaHoc", KhoaHoc.TenKhoaHoc);
            pr[1] = new SqlParameter(@"ThoiGianBatDau", KhoaHoc.ThoiGianBatDau);
           
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbKhoaHoc_KiemTraDuLieu", pr);
            if (all.Rows.Count > 0)
            {
                return true;
            }
            return false;
        }
//-----------------------------------------------------------//
        public static ETbKhoaHoc SelectByID(int MaKhoaHoc)
        {
            ETbKhoaHoc OTbKhoaHoc = new ETbKhoaHoc();
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"id",MaKhoaHoc);
            IDataReader idr = SqlHelper.ExecuteReader(CommandType.StoredProcedure,"TbKhoaHoc_SelectByID", pr);
            if (idr.Read())
              OTbKhoaHoc = GetOneTbKhoaHoc(idr);
            idr.Close();
            idr.Dispose();
            return OTbKhoaHoc;
        }
//-----------------------------------------------------------//
        private static ETbKhoaHoc GetOneTbKhoaHoc(IDataReader idr)
       {
            ETbKhoaHoc OTbKhoaHoc = new ETbKhoaHoc();
                if (idr["MaKhoaHoc"] != DBNull.Value)
                    OTbKhoaHoc.MaKhoaHoc = (int)idr["MaKhoaHoc"];
                if (idr["TenKhoaHoc"] != DBNull.Value)
                    OTbKhoaHoc.TenKhoaHoc = (string)idr["TenKhoaHoc"];
                if (idr["NoiDung"] != DBNull.Value)
                    OTbKhoaHoc.NoiDung = (string)idr["NoiDung"];
                if (idr["DiaDiemHoc"] != DBNull.Value)
                    OTbKhoaHoc.DiaDiemHoc = (string)idr["DiaDiemHoc"];
                if (idr["ThoiGianBatDau"] != DBNull.Value)
                    OTbKhoaHoc.ThoiGianBatDau = (DateTime)idr["ThoiGianBatDau"];
                if (idr["ThoiGianKetThuc"] != DBNull.Value)
                    OTbKhoaHoc.ThoiGianKetThuc = (DateTime)idr["ThoiGianKetThuc"];
            return OTbKhoaHoc;
       }
//-----------------------------------------------------------//
        public static bool TestByID(int MaKhoaHoc)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter(@"MaKhoaHoc",MaKhoaHoc);
            pr[1] = new SqlParameter(@"TestID",SqlDbType.Bit);
            pr[1].Direction = ParameterDirection.Output;
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure,"TbKhoaHoc_TestByID", pr);
            return Convert.ToBoolean(pr[1].Value);
        }
//-----------------------------------------------------------//
        public static List<ETbKhoaHoc> ListAll()
        {
            List<ETbKhoaHoc> list = new List<ETbKhoaHoc>();
            IDataReader idr = SqlHelper.ExecuteReader(CommandType.StoredProcedure, "TbKhoaHoc_SelectAll", null);
            while (idr.Read())
              list.Add(GetOneTbKhoaHoc(idr));
            if (idr.IsClosed == false)
            {
               idr.Close();
               idr.Dispose();
            }
            return list;
        }

        public static List<ETbKhoaHoc> ListTop(int Top)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"Top", Top);
            List<ETbKhoaHoc> list = new List<ETbKhoaHoc>();
            IDataReader idr = SqlHelper.ExecuteReader(CommandType.StoredProcedure, "TbKhoaHoc_SelectAll", pr);
            while (idr.Read())
              list.Add(GetOneTbKhoaHoc(idr));
            if (idr.IsClosed == false)
            {
               idr.Close();
               idr.Dispose();
            }
            return list;
        }
    }
 
}